package com.regulareport.common.utils.poi;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.regulareport.common.exception.ServiceException;
import com.regulareport.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.PaneInformation;

import java.io.ByteArrayInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author shkstart
 * @create 2025-09-05 14:23
 */
public class ExcelToJsonConverter {

        private static final ObjectMapper objectMapper = new ObjectMapper();

        /** 前端支持的对齐格式 */
        private static final String[] alignArr = {"CENTER","LEFT","RIGHT"};

        /** 前端支持的对齐格式 */
        private static final String[] valignArr = {"TOP","CENTER","BOTTOM"};

        /** 边框不存在 “NONE” */
        private static final String borderNo = "NONE";

        /**正则表达式模式：匹配数字和点组成的编号，然后是名称 */
        private static final Pattern pattern = Pattern.compile(".*?[:：]?([\\d.]+[a-z]?|[\\u2160-\\u216F]+)[\\s.]*(.*)?$");

    /**
         * 转换保存二进制转化为InputStream
         */
        public static String convertByteArrayWithInputStream(byte[] fileJson) throws Exception {
            InputStream input = new ByteArrayInputStream(fileJson);
            String fileData = "";
            try {
                 fileData = convertExcelWithStyles(input);
                 input.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            } finally {
                try {
                    input.close();
                } catch (IOException ex) {
                    throw new RuntimeException(ex);
                }
            }
            return fileData;
        }

        /**
         * 转换Excel为包含合并单元格和样式的JSON
         */
        public static String convertExcelWithStyles(InputStream inputStream) throws Exception {

                Workbook workbook = new HSSFWorkbook(inputStream);

                ObjectNode result = objectMapper.createObjectNode();
//                int sheetTotal = workbook.getNumberOfSheets();
//                for(int i = 0; i <workbook.getNumberOfSheets(); i++) {
                   Sheet sheet = workbook.getSheetAt(0);
                   ObjectNode sheetData = convertSheetWithFullStyles(sheet);
//                   result.set("sheet" + i, sheetData);
                    result.set("sheet1", sheetData);
//                }
            ObjectMapper mapper = new ObjectMapper();
            Map<String, Object> map = mapper.convertValue(result, new TypeReference<Map<String, Object>>() {});
            objectMapper.disable(SerializationFeature.INDENT_OUTPUT);
            return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(result);
        }

        /**
         * 转换单个工作表（完整样式支持）
         */
        private static ObjectNode convertSheetWithFullStyles(Sheet sheet) {
            ObjectNode sheetNode = objectMapper.createObjectNode();
            sheetNode.put("name", sheet.getSheetName());

            // 处理合并单元格
            List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
            ArrayNode mergeCellsArray = objectMapper.createArrayNode();
            ArrayNode mergeArray = objectMapper.createArrayNode();
            Set styleSet = new LinkedHashSet();
            for (CellRangeAddress mergedRegion : mergedRegions) {
                ObjectNode mergeNode = objectMapper.createObjectNode();
                mergeNode.put("range", mergedRegion.formatAsString());
                mergeNode.put("firstRow", mergedRegion.getFirstRow());
                mergeNode.put("lastRow", mergedRegion.getLastRow());
                mergeNode.put("firstColumn", mergedRegion.getFirstColumn());
                mergeNode.put("lastColumn", mergedRegion.getLastColumn());

                // 获取主单元格的值和样式
                Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
                if (firstRow != null) {
                    Cell firstCell = firstRow.getCell(mergedRegion.getFirstColumn());

                    if (firstCell != null) {
                        mergeNode.put("text", getCellValueAsString(firstCell));
                        mergeNode.set("style", extractCellStyle(firstCell));
                    }
                }
                mergeArray.add(mergedRegion.formatAsString());
                mergeCellsArray.add(mergeNode);
            }
            //获取冻结行列cell
            PaneInformation paneInfo = sheet.getPaneInformation();
            String a1Address = "";
            if (paneInfo != null) {
                System.out.println("paneInfo是否冻结: " + paneInfo.isFreezePane());
                int firstActiveCol = paneInfo.getVerticalSplitLeftColumn();
                int firstActiveRow = paneInfo.getHorizontalSplitTopRow();
                short colIndex =  paneInfo.getVerticalSplitPosition();
                short rowIndex = paneInfo.getHorizontalSplitPosition();
                System.out.println("firstActiveCol===========" + firstActiveCol);
                System.out.println("firstActiveRow===========" + firstActiveRow);
                System.out.println("colIndex===========" + colIndex);
                System.out.println("rowIndex===========" + rowIndex);
                a1Address = new CellReference(rowIndex, colIndex).formatAsString();
                String a1Address1  = new CellReference(firstActiveRow, firstActiveCol).formatAsString();
                System.out.println("活动冻结区域: " + a1Address);
                System.out.println("活动区域首单元格: " + a1Address1);
                sheetNode.put("freeze", a1Address);
            }
//            sheetNode.set("mergeCells", mergeCellsArray);

            // 创建合并单元格映射，用于后续检查
            Map<String, CellRangeAddress> mergeMap = new HashMap<>();
            for (CellRangeAddress mergedRegion : mergedRegions) {
                for (int row = mergedRegion.getFirstRow(); row <= mergedRegion.getLastRow(); row++) {
                    for (int col = mergedRegion.getFirstColumn(); col <= mergedRegion.getLastColumn(); col++) {
                        String key = row + "," + col;
                        mergeMap.put(key, mergedRegion);
                    }
                }
            }

            // 处理所有单元格数据
            ObjectNode sheetRowsNode = objectMapper.createObjectNode();
            ObjectNode colWidthNode = objectMapper.createObjectNode();
            int lastRowNum = sheet.getLastRowNum();
            int maxCellNum = 0;

            for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {

                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    // 处理空行
                    ObjectNode emptyRowNode = objectMapper.createObjectNode();
                    emptyRowNode.put("isEmpty", true);
                    sheetRowsNode.set(String.valueOf(rowNum), emptyRowNode);
                    continue;
                }

                int rowHeight =  (int) ((row.getHeightInPoints()/72)*96);

                ObjectNode rowNode = objectMapper.createObjectNode();
                rowNode.put("isEmpty", false);
                rowNode.put("height",rowHeight);

                ObjectNode rowCellsNode = objectMapper.createObjectNode();
                short lastCellNum = row.getLastCellNum();

                for (int colNum = 0; colNum < lastCellNum; colNum++) {
                    Cell cell = row.getCell(colNum);
                    //获取列col宽
                    if(rowNum == 0 && cell != null){
                        ObjectNode colWidthObject = objectMapper.createObjectNode();
                        int colWidth = (int) sheet.getColumnWidthInPixels(cell.getColumnIndex());
                        colWidthObject.put("width", colWidth);
                        colWidthNode.set(String.valueOf(colNum), colWidthObject);
                    }

                    String cellKey = rowNum + "," + colNum;

                    ObjectNode cellNode = objectMapper.createObjectNode();
                    // 检查是否是合并单元格的一部分
                    if (mergeMap.containsKey(cellKey)) {
                        CellRangeAddress mergedRegion = mergeMap.get(cellKey);
                        cellNode.put("isMerged", true);
                        cellNode.put("isMaster",
                                mergedRegion.getFirstRow() == rowNum &&
                                        mergedRegion.getFirstColumn() == colNum);
                        //通过坐标获取合并单元格首行首列值
                        HSSFRow mergeFirstRow = (HSSFRow) sheet.getRow(mergedRegion.getFirstRow());
                        Cell mergeFirstCell = mergeFirstRow.getCell(mergedRegion.getFirstColumn());
                        cellNode.put("mergeText1", getCellValueAsString(mergeFirstCell));

                        // 如果是主单元格，保存值和样式
                        if (cellNode.get("isMaster").asBoolean() && cell != null) {
                            cellNode.put("text", getCellValueAsString(cell));
                            cellNode.set("style", extractCellStyle(cell));
                            //merge赋值
                            ArrayNode rowCellArray = objectMapper.createArrayNode();
                            rowCellArray.add(Integer.valueOf(mergedRegion.getLastRow() - rowNum));
                            rowCellArray.add(mergedRegion.getLastColumn()-mergedRegion.getFirstColumn());
                            cellNode.set("merge", rowCellArray);
                        } else {
                            cellNode.set("style", extractCellStyle(cell));
                        }
                        //判断是否为公式并获取
                        if (cell != null && cell.getCellType() == CellType.FORMULA) {
                            String formula = cell.getCellFormula();
                            cellNode.put("formula", formula);
                        }
                        //背景色设置，指标定义需要使用
                        cellNode.set("bgColor", obtainCellStyleBgColor(cell).get("bgcolor"));
                        cellNode.put("editable", false);
                        if(maxCellNum < colNum){
                            maxCellNum = colNum;
                        }
                    } else if (cell != null) {
                        // 普通单元格
                        cellNode.put("isMerged", false);
                        cellNode.put("isMaster", false);
                        cellNode.put("text", getCellValueAsString(cell));
                        cellNode.set("style", extractCellStyle(cell));
//                        cellNode.put("editable", IsCompleteCellStyleBorder(cell));
                         //判断是否为公式并获取
                        if (cell.getCellType() == CellType.FORMULA) {
                            String formula = cell.getCellFormula();
                            cellNode.put("formula", formula);
                        }
                        cellNode.set("bgColor", obtainCellStyleBgColor(cell).get("bgcolor"));
                        if(IsExistCellStyleRightBorder(cell) || StringUtils.isNotEmpty(getCellValueAsString(cell))){ //是否存在右边框或存在值
                            if(maxCellNum < colNum){
                                maxCellNum = colNum;
                            }
                        }
                        cellNode.put("editable", judgeCellStyleEditable(cell));
                    } else {
                        // 空单元格
                        cellNode.put("editable", false);
                        cellNode.put("isMerged", false);
                        cellNode.put("isMaster", false);
                        cellNode.put("text", "");
//                        cellNode.set("style", objectMapper.createObjectNode());
                        cellNode.set("style", extractCellStyle(cell));
                    }

                    if(cellNode != null && cellNode.findValue("style") != null) {
                        styleSet.add(cellNode.findValue("style"));
                        cellNode.set("style", objectMapper.valueToTree(findPosition(styleSet, cellNode.findValue("style"))));
                        rowCellsNode.set(String.valueOf(colNum),cellNode);
                    }

                }
                rowNode.set("cells", rowCellsNode);
//                rowsArray.add(rowNode);
                sheetRowsNode.set(String.valueOf(rowNum), rowNode);
            }
            // 将LinkedHashSet转换为List
            List list = (List) styleSet.stream().collect(Collectors.toList());

            // 使用Jackson将List转换为ArrayNode
            ObjectMapper mapper = new ObjectMapper();
            ArrayNode arrayNodeTyle = mapper.valueToTree(list);
            sheetNode.set("merges", mergeArray);
            sheetNode.set("styles", arrayNodeTyle);
            sheetNode.set("rows", sheetRowsNode);
            sheetNode.set("cols", colWidthNode);
            System.out.println("报表编号=========="+sheet.getSheetName()+", 总行数rowNum==== " + lastRowNum);
            System.out.println("报表编号=========="+sheet.getSheetName()+", 最大列数cellNum==== " + maxCellNum);
            sheetNode.put("rowNum", lastRowNum+1); //总计多少行
            sheetNode.put("cellNum", maxCellNum+1); //最多多少列
            return sheetNode;
        }

        /**
         * 提取单元格样式信息
         */
        private static ObjectNode extractCellStyle(Cell cell) {
            ObjectNode styleNode = objectMapper.createObjectNode();
            if (cell == null) return styleNode;

            HSSFCellStyle cellStyle = (HSSFCellStyle) cell.getCellStyle();
            HSSFWorkbook workbook = (HSSFWorkbook) cell.getSheet().getWorkbook();
            // 背景颜色
            styleNode = obtainCellStyleBgColor(cell);
            // 字体样式
            try {
                Font font = workbook.getFontAt(cellStyle.getFontIndex());
                if (font != null) {
                    ObjectNode fontNode = objectMapper.createObjectNode();
                    fontNode.put("size", font.getFontHeightInPoints());
                    fontNode.put("bold", font.getBold());
                    fontNode.put("italic", font.getItalic());
                    fontNode.put("underline", font.getUnderline());

                    // 字体颜色
//                    short fontColor = font.getColor();
                    styleNode.set("font", fontNode);
                }
            } catch (Exception e) {
                // 忽略字体提取错误
                e.printStackTrace();
            }
            // 边框样式
//            obtainCellStyleBorderNo(cell, styleNode);
            try {
                Map borderNode = new HashMap();
                //如果四条边框同时不存在，不提供border
                if(!cellStyle.getBorderTop().toString().equals(borderNo) || !cellStyle.getBorderLeft().toString().equals(borderNo)
                        || !cellStyle.getBorderRight().toString().equals(borderNo) || !cellStyle.getBorderBottom().toString().equals(borderNo)) {
                    borderNode.put("top", getBorderStyle(cellStyle.getBorderTop(), cellStyle.getTopBorderColor()));
                    borderNode.put("bottom", getBorderStyle(cellStyle.getBorderBottom(), cellStyle.getBottomBorderColor()));
                    borderNode.put("left", getBorderStyle(cellStyle.getBorderLeft(), cellStyle.getLeftBorderColor()));
                    borderNode.put("right", getBorderStyle(cellStyle.getBorderRight(), cellStyle.getRightBorderColor()));
                    styleNode.set("border", objectMapper.valueToTree(borderNode));
                }
            } catch (Exception e) {
                // 忽略边框提取错误
                throw new ServiceException(e.getMessage());
            }

            // 对齐方式
            try {
                styleNode.put("align", Arrays.asList(alignArr).contains(cellStyle.getAlignment().toString()) ? cellStyle.getAlignment().toString().toLowerCase():"left");
                if(cellStyle.getVerticalAlignment()!=null && cellStyle.getVerticalAlignment().toString().equals("CENTER")) {
                    styleNode.put("valign", "middle");
                }else{
                    styleNode.put("valign", Arrays.asList(valignArr).contains(cellStyle.getVerticalAlignment().toString()) ? cellStyle.getVerticalAlignment().toString().toLowerCase():"middle");
                }
            } catch (Exception e) {
                // 忽略对齐方式提取错误
                throw new ServiceException(e.getMessage());
            }
            //内容是否换行
            styleNode.put("textwrap", cellStyle.getWrapText());
            boolean hasBackslash = containsBackslash(getCellValueAsString(cell));
            String regex2 = "\\s+[\\u4e00-\\u9fa5]+\\n\\n[\\u4e00-\\u9fa5]+";
            Pattern.matches(regex2, getCellValueAsString(cell));
            //是否是合并单元格，同时反斜杠分割合并单元格
            if(cell != null && Pattern.matches(regex2, getCellValueAsString(cell))){
                ArrayNode arrayNode = objectMapper.createArrayNode();
                arrayNode.add("solid");
                arrayNode.add("#000000");
                styleNode.set("diagonal", arrayNode);
            }

            return styleNode;
        }

        /**
         * 检查字符串是否包含反斜杠
         */
        private static boolean containsBackslash(String value) {
            return value != null && value.contains("\\");
        }

        // 背景颜色
        private static ObjectNode obtainCellStyleBgColor(Cell cell) {
            ObjectNode styleNode = objectMapper.createObjectNode();
            if (cell == null) return styleNode;
            HSSFCellStyle cellStyle = (HSSFCellStyle) cell.getCellStyle();
            String customHexColor = "";
            try {
                if (cellStyle != null && cellStyle.getFillPattern() == FillPatternType.SOLID_FOREGROUND) {
                    HSSFColor bgColor = cellStyle.getFillForegroundColorColor();
                    cellStyle.setLocked(false);
                    if (bgColor != null) {
                        customHexColor = String.format("#%02x%02x%02x",
                                bgColor.getTriplet()[0],
                                bgColor.getTriplet()[1],
                                bgColor.getTriplet()[2]);
                    }
//                    if(customHexColor.equals("#99ccff") || customHexColor.equals("#9bc2e6") || customHexColor.equals("#0066cc")) { //模板蓝色
                    if(cell != null && (cell.getCellType() == CellType.FORMULA || customHexColor.equals("#99ccff") || customHexColor.equals("#9bc2e6") || customHexColor.equals("#0066cc"))) { //模板蓝色
                        customHexColor = "#9cc2e6";  //页面蓝色
                    }

//                    if(customHexColor.equals("#c0c0c0") || customHexColor.equals("#bfbfbf") || customHexColor.equals("#969696")){//模板灰色
                    if(cell != null && cell.getCellType() != CellType.FORMULA && !(customHexColor.equals("#ffffff")|| customHexColor.equals("#9cc2e6") || bgColor == null)){//模板灰色
//                        System.out.println("灰色灰色 ==========  rowNUm = " + cell.getRowIndex() + ", colNum = "+ cell.getColumnIndex());
                        customHexColor = "#d0cecf";  //页面灰色
                    }
                    styleNode.put("bgcolor", customHexColor);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return styleNode;
        }

    private static ObjectNode obtainCellStyleBorderNo(Cell cell, ObjectNode styleNode) {
        if (cell == null) return styleNode;
        HSSFCellStyle cellStyle = (HSSFCellStyle) cell.getCellStyle();
        // 边框样式
        try {
            Map borderNode = new HashMap();
            //如果四条边框同时不存在，不提供border
            if(!cellStyle.getBorderTop().toString().equals(borderNo) || !cellStyle.getBorderLeft().toString().equals(borderNo)
                    || !cellStyle.getBorderRight().toString().equals(borderNo) || !cellStyle.getBorderBottom().toString().equals(borderNo)) {
                borderNode.put("top", getBorderStyle(cellStyle.getBorderTop(), cellStyle.getTopBorderColor()));
                borderNode.put("bottom", getBorderStyle(cellStyle.getBorderBottom(), cellStyle.getBottomBorderColor()));
                borderNode.put("left", getBorderStyle(cellStyle.getBorderLeft(), cellStyle.getLeftBorderColor()));
                borderNode.put("right", getBorderStyle(cellStyle.getBorderRight(), cellStyle.getRightBorderColor()));
                styleNode.set("border", objectMapper.valueToTree(borderNode));
            }
        } catch (Exception e) {
            // 忽略边框提取错误
            e.printStackTrace();
        }
        return styleNode;
    }

    private static boolean  isCompleteCellStyleBorder(Cell cell){
        HSSFCellStyle cellStyle = (HSSFCellStyle) cell.getCellStyle();
        if(cellStyle != null && (cellStyle.getBorderTop().toString().equals(borderNo) || cellStyle.getBorderLeft().toString().equals(borderNo)
                || cellStyle.getBorderRight().toString().equals(borderNo) || cellStyle.getBorderBottom().toString().equals(borderNo))) {
            return false;
        }
        return true;
    }

    //是否存在右边框 调用方已判断cell不为null
    private static boolean IsExistCellStyleRightBorder(Cell cell){

        HSSFCellStyle cellStyle = (HSSFCellStyle) cell.getCellStyle();
        if(cellStyle != null && !cellStyle.getBorderRight().toString().equals(borderNo)) {
            return true;
        }
        return false;
    }

    //判断单元格是否可编辑
    private static boolean judgeCellStyleEditable(Cell cell){
        boolean  editable = true ;
        if(StringUtils.isNotEmpty(getCellValueAsString(cell))){
            editable = false;
        }
        if(obtainCellStyleBgColor(cell) != null && obtainCellStyleBgColor(cell).get("bgcolor") != null && ("#9cc2e6".equals(obtainCellStyleBgColor(cell).get("bgcolor").asText()) || "#d0cecf".equals(obtainCellStyleBgColor(cell).get("bgcolor").asText()))){
            editable = false;
        }
        if(!isCompleteCellStyleBorder(cell)) {
            editable = false;
        }
        return editable;
    }

        /**
         * 获取边框样式名称
         */
        private static List getBorderStyle(BorderStyle borderStyle, short borderColor) {
            List bordStyles = new ArrayList();
            if(borderStyle != null && !borderStyle.equals(BorderStyle.NONE)) {
                bordStyles.add(borderStyle.toString().toLowerCase() != "thin" ? "thin" : borderStyle.toString().toLowerCase());
                bordStyles.add(borderColor == 0 ? "#000040" : colorConverter(borderColor));
            } else {
                bordStyles.add("none");
                bordStyles.add("#e6e6e6"); //灰色
            }

            return bordStyles;
        }

        /**
         * 获取单元格值的字符串表示
         */
        public static String getCellValueAsString(Cell cell) {
            if (cell == null) return "";

            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return cell.getDateCellValue().toString();
                    } else {
                        // 避免科学计数法
                        double num = cell.getNumericCellValue();
                        if (num == (long) num) {
                            return String.valueOf((long) num);
                        } else {
                            return String.valueOf(num);
                        }
                    }
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case FORMULA:
                    try {
                        return String.valueOf(cell.getNumericCellValue());
                    } catch (Exception e) {
                        try {
                            return cell.getStringCellValue();
                        } catch (Exception ex) {
                            return cell.getCellFormula();
                        }
                    }
                case BLANK:
                    return "";
                default:
                    return "";
            }
        }

        /**
         * 获取列名（A, B, C, ...）
         */
        private static String getColumnName(int columnIndex) {
            StringBuilder columnName = new StringBuilder();
            while (columnIndex >= 0) {
                int remainder = columnIndex % 26;
                columnName.insert(0, (char) ('A' + remainder));
                columnIndex = (columnIndex / 26) - 1;
            }
            return columnName.toString();
        }

        /**
         * 保存JSON到文件
         */
        public void saveJsonToFile(String jsonData, String outputPath) throws IOException {
            try (FileWriter writer = new FileWriter(outputPath)) {
                writer.write(jsonData);
            }
        }

        /**
         * 获取LinkHashSet中style元素的位置
         * @param set
         * @param element
         * @return
         * @param <T>
         */
        private static <T> int findPosition(Set<T> set, T element) {
                int position = 0;
                boolean found = false;
                Iterator<T> iterator = set.iterator();
                while (iterator.hasNext()) {
                    T currentElement = iterator.next();
                    try {
                        if (currentElement.equals(element)) {
                            found = true;
                            break;
                        }
                    } catch (Exception e) {
                        System.out.println("currentElement============="+currentElement);
                        System.out.println("-------"+element);
                        throw new RuntimeException(e);
                    }
                    position++;
                }
                return found ? position : 0; // 如果未找到，默认0
            }

        /**
         * short颜色转换为16位格式
         */
        private static String colorConverter(short colorValue) {
            // 将short提升为int，然后转换为16进制字符串
            String hexColor = Integer.toHexString(colorValue & 0xFFFF); // 使用& 0xFFFF确保只取低16位

            // 格式化为标准的#RRGGBB格式
            // 注意：如果hexColor长度小于6，需要在前面补0
            hexColor = String.format("#%06X", colorValue & 0xFFFF);
            return hexColor;
        }

        /**
         * 解析合并单元格坐标
         */
        public static List<Map> getCellsAddress(String mergesRef) {
            String[] mergeAddrArr = StringUtils.isNotEmpty(mergesRef) ? mergesRef.split(":"): null;
            List coordinateList = null;
            if(mergeAddrArr != null && mergeAddrArr.length > 0){
                coordinateList = new ArrayList<>();
                for(String addr : mergeAddrArr){
                   Map coordinateMap = new HashMap();
                    // 使用POI内置工具类转换
                    CellReference ref = new CellReference(addr);
                    coordinateMap.put("row", ref.getRow());// 获取0-based行号（A6→5）
                    coordinateMap.put("col", (int)ref.getCol());;// 获取0-based列号（A→0）
                    coordinateList.add(coordinateMap);
                }
            }
            return coordinateList;
        }

        public static List<ObjectNode> parseStrings(String[] strings) {
            List<ObjectNode> results = new ArrayList<>();

            // 正则表达式模式：匹配数字和点组成的编号，然后是名称
//            Pattern pattern = Pattern.compile("(?:.*?[:：])?((?:\\d+\\.|\\d)+\\d*)(.*)");

            for (String str : strings) {
                Matcher matcher = pattern.matcher(str);
                ObjectNode identifiName = objectMapper.createObjectNode();

                if (matcher.find()) {
                    String number = matcher.group(1);
                    String name = matcher.group(2).trim();

                    // 处理特殊情况：编号后紧跟着数字的情况（如字符串5）
                    if (!name.isEmpty() && Character.isDigit(name.charAt(0))) {
                        // 找出名称中第一个非数字非点的字符位置
                        int splitIndex = 0;
                        while (splitIndex < name.length() &&
                                (Character.isDigit(name.charAt(splitIndex)) ||
                                        name.charAt(splitIndex) == '.')) {
                            splitIndex++;
                        }

                        if (splitIndex > 0) {
                            number += name.substring(0, splitIndex);
                            name = name.substring(splitIndex).trim();
                        }
                    }
                    identifiName.put("number", number);
                    identifiName.put("name", name);
                } else {
                    // 如果没有匹配到模式，将整个字符串作为名称
                    identifiName.put("number", "");
                    identifiName.put("name", str);
                }
                results.add(identifiName);
            }
            return results;
        }

        /** 解析SF1900 中文字母和数字  转换为  数字和中文字母 */
        public static String swapTextAndNumber(String input, String indexDesSecCol) {
            // 匹配末尾的数字（1-4位）
            Matcher numMatcher = Pattern.compile("(\\d{1,4})$").matcher(input);
            // 匹配数字前的所有内容（中文、英文、符号等）
            Matcher textMatcher = Pattern.compile("^(.*?)(?=\\d{1,4}$)").matcher(input);

            if (numMatcher.find() && textMatcher.find()) {
                return numMatcher.group(1) + textMatcher.group(1).substring(0, textMatcher.group(1).indexOf("_"));
            } else if(!numMatcher.find() && !textMatcher.find() && !StringUtils.isEmpty(indexDesSecCol) ){ //如果编号单元格为null的话，补充为前端传递的罗马数字
                return indexDesSecCol + input.substring(0, input.indexOf("_"));
            }
            return null; // 未匹配则返回null
        }

        public static String getCellReference(int row, int col) {
            Integer rowInt = Integer.valueOf(row);
            Integer colInt = Integer.valueOf(col);
            // 创建CellReference对象
            CellReference cellRef = new CellReference(rowInt, colInt);

            // 获取标准Excel格式的单元格引用
            String excelRef = cellRef.formatAsString();
            return excelRef;
        }

//        public static String expandSumFormula(String formula){
//            if (formula.startsWith("SUM(")) {
//                String range = formula.substring(4, formula.length() - 1);
//                String[] parts = range.split(":");
//                if (parts.length != 2) return formula;
//
//                try {
//                    CellReference start = new CellReference(parts[0]);
//                    CellReference end = new CellReference(parts[1]);
//                    List<String> cells = new ArrayList<>();
//
//                    for (int col = start.getCol(); col <= end.getCol(); col++) {
//                        cells.add(new CellReference(start.getRow(), col).formatAsString());
//                    }
//                    return String.join("+", cells);
//                } catch (Exception e) {
//                    return formula;
//                }
//            } else if(formula){
//
//            }
//
//            return "";
//        }


}
